/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     16.06.2011 12:26:37                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Auto') and o.name = 'FK_AUTO_AUTOCLASS_AUTOCLAS')
alter table Auto
   drop constraint FK_AUTO_AUTOCLASS_AUTOCLAS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AutoDamage') and o.name = 'FK_AUTODAMA_RELATIONS_AUTOORDE')
alter table AutoDamage
   drop constraint FK_AUTODAMA_RELATIONS_AUTOORDE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AutoOrder') and o.name = 'FK_AUTOORDE_AUTORELAT_AUTO')
alter table AutoOrder
   drop constraint FK_AUTOORDE_AUTORELAT_AUTO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AutoOrder') and o.name = 'FK_AUTOORDE_AUTORETUR_PAYMENT')
alter table AutoOrder
   drop constraint FK_AUTOORDE_AUTORETUR_PAYMENT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AutoOrder') and o.name = 'FK_AUTOORDE_CHOSEAUTO_AUTOCLAS')
alter table AutoOrder
   drop constraint FK_AUTOORDE_CHOSEAUTO_AUTOCLAS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AutoOrder') and o.name = 'FK_AUTOORDE_CHOSERETU_OFFICE')
alter table AutoOrder
   drop constraint FK_AUTOORDE_CHOSERETU_OFFICE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AutoOrder') and o.name = 'FK_AUTOORDE_MANAGERAU_SYSTEMUS')
alter table AutoOrder
   drop constraint FK_AUTOORDE_MANAGERAU_SYSTEMUS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AutoOrder') and o.name = 'FK_AUTOORDE_MANAGERWI_SYSTEMUS')
alter table AutoOrder
   drop constraint FK_AUTOORDE_MANAGERWI_SYSTEMUS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AutoOrder') and o.name = 'FK_AUTOORDE_MECHANICC_SYSTEMUS')
alter table AutoOrder
   drop constraint FK_AUTOORDE_MECHANICC_SYSTEMUS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AutoOrder') and o.name = 'FK_AUTOORDE_OFFICEREL_OFFICE')
alter table AutoOrder
   drop constraint FK_AUTOORDE_OFFICEREL_OFFICE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AutoOrder') and o.name = 'FK_AUTOORDE_ORDERRELA_CLIENT')
alter table AutoOrder
   drop constraint FK_AUTOORDE_ORDERRELA_CLIENT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('City') and o.name = 'FK_CITY_REGIONCIT_REGION')
alter table City
   drop constraint FK_CITY_REGIONCIT_REGION
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ClientAddress') and o.name = 'FK_CLIENTAD_CLIENTADD_CLIENT')
alter table ClientAddress
   drop constraint FK_CLIENTAD_CLIENTADD_CLIENT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ClientAddress') and o.name = 'FK_CLIENTAD_CLIENTADD_STREET')
alter table ClientAddress
   drop constraint FK_CLIENTAD_CLIENTADD_STREET
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Employee') and o.name = 'FK_EMPLOYEE_EMPLOYEEM_SYSTEMUS')
alter table Employee
   drop constraint FK_EMPLOYEE_EMPLOYEEM_SYSTEMUS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EmployeeAddress') and o.name = 'FK_EMPLOYEE_EMPLOYEEA_EMPLOYEE')
alter table EmployeeAddress
   drop constraint FK_EMPLOYEE_EMPLOYEEA_EMPLOYEE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EmployeeAddress') and o.name = 'FK_EMPLOYEE_EMPLOYEEA_STREET')
alter table EmployeeAddress
   drop constraint FK_EMPLOYEE_EMPLOYEEA_STREET
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Office') and o.name = 'FK_OFFICE_OFFICESTR_STREET')
alter table Office
   drop constraint FK_OFFICE_OFFICESTR_STREET
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Payment') and o.name = 'FK_PAYMENT_AUTORETUR_AUTOORDE')
alter table Payment
   drop constraint FK_PAYMENT_AUTORETUR_AUTOORDE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Region') and o.name = 'FK_REGION_COUNTRYRE_COUNTRY')
alter table Region
   drop constraint FK_REGION_COUNTRYRE_COUNTRY
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Street') and o.name = 'FK_STREET_CITYSTREE_CITY')
alter table Street
   drop constraint FK_STREET_CITYSTREE_CITY
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SystemUser') and o.name = 'FK_SYSTEMUS_SYSTEMUSE_OFFICE')
alter table SystemUser
   drop constraint FK_SYSTEMUS_SYSTEMUSE_OFFICE
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Auto')
            and   name  = 'AutoClassRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index Auto.AutoClassRelationship_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Auto')
            and   type = 'U')
   drop table Auto
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AutoClass')
            and   type = 'U')
   drop table AutoClass
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('AutoDamage')
            and   name  = 'Relationship_21_FK'
            and   indid > 0
            and   indid < 255)
   drop index AutoDamage.Relationship_21_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AutoDamage')
            and   type = 'U')
   drop table AutoDamage
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('AutoOrder')
            and   name  = 'ChoseReturningOfficeRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index AutoOrder.ChoseReturningOfficeRelationship_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('AutoOrder')
            and   name  = 'AutoReturningPaymentRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index AutoOrder.AutoReturningPaymentRelationship_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('AutoOrder')
            and   name  = 'ManagerAutoReturningRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index AutoOrder.ManagerAutoReturningRelationship_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('AutoOrder')
            and   name  = 'MechanicCheckingAutoRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index AutoOrder.MechanicCheckingAutoRelationship_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('AutoOrder')
            and   name  = 'ManagerWithdrawal_FK'
            and   indid > 0
            and   indid < 255)
   drop index AutoOrder.ManagerWithdrawal_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('AutoOrder')
            and   name  = 'ChoseAutoClassRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index AutoOrder.ChoseAutoClassRelationship_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('AutoOrder')
            and   name  = 'AutoRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index AutoOrder.AutoRelationship_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('AutoOrder')
            and   name  = 'OfficeRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index AutoOrder.OfficeRelationship_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('AutoOrder')
            and   name  = 'OrderRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index AutoOrder.OrderRelationship_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AutoOrder')
            and   type = 'U')
   drop table AutoOrder
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('City')
            and   name  = 'RegionCityRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index City.RegionCityRelationship_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('City')
            and   type = 'U')
   drop table City
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Client')
            and   type = 'U')
   drop table Client
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ClientAddress')
            and   name  = 'ClientAddressClientRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index ClientAddress.ClientAddressClientRelationship_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ClientAddress')
            and   name  = 'ClientAddressStreetRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index ClientAddress.ClientAddressStreetRelationship_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ClientAddress')
            and   type = 'U')
   drop table ClientAddress
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Country')
            and   type = 'U')
   drop table Country
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Employee')
            and   name  = 'EmployeeMechanicRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index Employee.EmployeeMechanicRelationship_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Employee')
            and   type = 'U')
   drop table Employee
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('EmployeeAddress')
            and   name  = 'EmployeeAddressEmployeeRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index EmployeeAddress.EmployeeAddressEmployeeRelationship_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('EmployeeAddress')
            and   name  = 'EmployeeAddressStreetRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index EmployeeAddress.EmployeeAddressStreetRelationship_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EmployeeAddress')
            and   type = 'U')
   drop table EmployeeAddress
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Office')
            and   name  = 'OfficeStreetRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index Office.OfficeStreetRelationship_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Office')
            and   type = 'U')
   drop table Office
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Payment')
            and   name  = 'AutoReturningPaymentRelationship2_FK'
            and   indid > 0
            and   indid < 255)
   drop index Payment.AutoReturningPaymentRelationship2_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Payment')
            and   type = 'U')
   drop table Payment
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Region')
            and   name  = 'CountryRegion_FK'
            and   indid > 0
            and   indid < 255)
   drop index Region.CountryRegion_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Region')
            and   type = 'U')
   drop table Region
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Street')
            and   name  = 'CityStreetRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index Street.CityStreetRelationship_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Street')
            and   type = 'U')
   drop table Street
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('SystemUser')
            and   name  = 'SystemUserWorksInOfficeRelationship_FK'
            and   indid > 0
            and   indid < 255)
   drop index SystemUser.SystemUserWorksInOfficeRelationship_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SystemUser')
            and   type = 'U')
   drop table SystemUser
go

/*==============================================================*/
/* Table: Auto                                                  */
/*==============================================================*/
create table Auto (
   Auto_ID              numeric              identity,
   AutoClass_ID         numeric              null,
   AutoModel            nvarchar(128)        not null,
   AutoNumber           nvarchar(10)         not null,
   AutoColor            nvarchar(50)         null,
   AutoInfo             text                 null,
   AutoMileage          int                  null,
   AutoMileageUpdated   datetime             null,
   AutoPetrolTankVolume int                  not null,
   AutoPetrolTankFilled int                  null,
   AutoPhoto            image                null,
   constraint PK_AUTO primary key nonclustered (Auto_ID)
)
go

/*==============================================================*/
/* Index: AutoClassRelationship_FK                              */
/*==============================================================*/
create index AutoClassRelationship_FK on Auto (
AutoClass_ID ASC
)
go

/*==============================================================*/
/* Table: AutoClass                                             */
/*==============================================================*/
create table AutoClass (
   AutoClass_ID         numeric              identity,
   AutoClassName        nvarchar(256)        not null,
   AutoClassPhoto       image                null,
   AutoClassInfo        text                 null,
   AutoClassPriceMileageRate money                null,
   AutoClassPriceDailyRate money                null,
   constraint PK_AUTOCLASS primary key nonclustered (AutoClass_ID)
)
go

/*==============================================================*/
/* Table: AutoDamage                                            */
/*==============================================================*/
create table AutoDamage (
   AutoDamage_ID        numeric              identity,
   AutoOrder_ID         numeric              null,
   AutoDamageDescription text                 not null,
   AutoDamageEstimatedCost money                null,
   constraint PK_AUTODAMAGE primary key nonclustered (AutoDamage_ID)
)
go

/*==============================================================*/
/* Index: Relationship_21_FK                                    */
/*==============================================================*/
create index Relationship_21_FK on AutoDamage (
AutoOrder_ID ASC
)
go

/*==============================================================*/
/* Table: AutoOrder                                             */
/*==============================================================*/
create table AutoOrder (
   AutoOrder_ID         numeric              identity,
   Office_ID            numeric              null,
   Payment_ID           numeric              null,
   Auto_ID              numeric              null,
   Client_ID            numeric              null,
   SystemUser_ID        numeric              null,
   Sys_SystemUser_ID    numeric              null,
   Sys_SystemUser_ID2   numeric              null,
   AutoClass_ID         numeric              null,
   Off_Office_ID        numeric              null,
   AutoOrderRequestApproved bit                  not null,
   AutoOrderRequestDT   datetime             null,
   AutoOrderClientEmail nvarchar(256)        null,
   AutoOrderWithdrawalDT datetime             null,
   AutoOrderReturningDT datetime             null,
   AutoOrderTypeOfPayment nvarchar(50)         not null,
   AutoOrderMileage     int                  null,
   constraint PK_AUTOORDER primary key nonclustered (AutoOrder_ID)
)
go

/*==============================================================*/
/* Index: OrderRelationship_FK                                  */
/*==============================================================*/
create index OrderRelationship_FK on AutoOrder (
Client_ID ASC
)
go

/*==============================================================*/
/* Index: OfficeRelationship_FK                                 */
/*==============================================================*/
create index OfficeRelationship_FK on AutoOrder (
Off_Office_ID ASC
)
go

/*==============================================================*/
/* Index: AutoRelationship_FK                                   */
/*==============================================================*/
create index AutoRelationship_FK on AutoOrder (
Auto_ID ASC
)
go

/*==============================================================*/
/* Index: ChoseAutoClassRelationship_FK                         */
/*==============================================================*/
create index ChoseAutoClassRelationship_FK on AutoOrder (
AutoClass_ID ASC
)
go

/*==============================================================*/
/* Index: ManagerWithdrawal_FK                                  */
/*==============================================================*/
create index ManagerWithdrawal_FK on AutoOrder (
SystemUser_ID ASC
)
go

/*==============================================================*/
/* Index: MechanicCheckingAutoRelationship_FK                   */
/*==============================================================*/
create index MechanicCheckingAutoRelationship_FK on AutoOrder (
Sys_SystemUser_ID2 ASC
)
go

/*==============================================================*/
/* Index: ManagerAutoReturningRelationship_FK                   */
/*==============================================================*/
create index ManagerAutoReturningRelationship_FK on AutoOrder (
Sys_SystemUser_ID ASC
)
go

/*==============================================================*/
/* Index: AutoReturningPaymentRelationship_FK                   */
/*==============================================================*/
create index AutoReturningPaymentRelationship_FK on AutoOrder (
Payment_ID ASC
)
go

/*==============================================================*/
/* Index: ChoseReturningOfficeRelationship_FK                   */
/*==============================================================*/
create index ChoseReturningOfficeRelationship_FK on AutoOrder (
Office_ID ASC
)
go

/*==============================================================*/
/* Table: City                                                  */
/*==============================================================*/
create table City (
   City_ID              numeric              identity,
   Region_ID            numeric              null,
   CityName             nvarchar(256)        not null,
   constraint PK_CITY primary key nonclustered (City_ID)
)
go

/*==============================================================*/
/* Index: RegionCityRelationship_FK                             */
/*==============================================================*/
create index RegionCityRelationship_FK on City (
Region_ID ASC
)
go

/*==============================================================*/
/* Table: Client                                                */
/*==============================================================*/
create table Client (
   Client_ID            numeric              identity,
   ClientLastName       nvarchar(50)         not null,
   ClientFirstName      nvarchar(50)         not null,
   ClientPatroname      nvarchar(50)         not null,
   ClientBirthDT        datetime             null,
   ClientGender         char(1)              not null,
   ClientPhone          nvarchar(15)         null,
   ClientPhoto          image                null,
   ClientInfo           text                 null,
   ClientEmail          nvarchar(256)        null,
   ClientPassportNumber nvarchar(20)         null,
   ClientInsuranceNumber nvarchar(20)         null,
   constraint PK_CLIENT primary key nonclustered (Client_ID)
)
go

/*==============================================================*/
/* Table: ClientAddress                                         */
/*==============================================================*/
create table ClientAddress (
   ClientAddress_ID     numeric              identity,
   Client_ID            numeric              null,
   Street_ID            numeric              null,
   ClientAddressBlock   int                  null,
   ClientAddressHouse   int                  null,
   ClientAddressFlat    int                  null,
   ClientAddressPostIndex int                  null,
   constraint PK_CLIENTADDRESS primary key nonclustered (ClientAddress_ID)
)
go

/*==============================================================*/
/* Index: ClientAddressStreetRelationship_FK                    */
/*==============================================================*/
create index ClientAddressStreetRelationship_FK on ClientAddress (
Street_ID ASC
)
go

/*==============================================================*/
/* Index: ClientAddressClientRelationship_FK                    */
/*==============================================================*/
create index ClientAddressClientRelationship_FK on ClientAddress (
Client_ID ASC
)
go

/*==============================================================*/
/* Table: Country                                               */
/*==============================================================*/
create table Country (
   Country_ID           numeric              identity,
   CountryName          nvarchar(256)        not null,
   constraint PK_COUNTRY primary key nonclustered (Country_ID)
)
go

/*==============================================================*/
/* Table: Employee                                              */
/*==============================================================*/
create table Employee (
   Employee_ID          numeric              identity,
   SystemUser_ID        numeric              null,
   EmployeeLastName     nvarchar(50)         not null,
   EmployeeFirstName    nvarchar(50)         not null,
   EmployeePatroname    nvarchar(50)         not null,
   EmployeeGender       char(1)              not null,
   EmployeePhone        nvarchar(15)         null,
   EmployeePhoto        image                null,
   EmployeeBirthDT      datetime             not null,
   EmployeeHireDT       datetime             not null,
   EmployeeSalary       money                null,
   EmployeeInfo         text                 null,
   EmployeePosition     nvarchar(50)         not null,
   constraint PK_EMPLOYEE primary key nonclustered (Employee_ID)
)
go

/*==============================================================*/
/* Index: EmployeeMechanicRelationship_FK                       */
/*==============================================================*/
create index EmployeeMechanicRelationship_FK on Employee (
SystemUser_ID ASC
)
go

/*==============================================================*/
/* Table: EmployeeAddress                                       */
/*==============================================================*/
create table EmployeeAddress (
   EmployeeAddress_ID   numeric              identity,
   Street_ID            numeric              null,
   Employee_ID          numeric              null,
   EmployeeAddressBlock int                  null,
   EmployeeAddressHouse int                  null,
   EmployeeAddressFlat  int                  null,
   EmployeeAddressPostIndex int                  null,
   constraint PK_EMPLOYEEADDRESS primary key nonclustered (EmployeeAddress_ID)
)
go

/*==============================================================*/
/* Index: EmployeeAddressStreetRelationship_FK                  */
/*==============================================================*/
create index EmployeeAddressStreetRelationship_FK on EmployeeAddress (
Street_ID ASC
)
go

/*==============================================================*/
/* Index: EmployeeAddressEmployeeRelationship_FK                */
/*==============================================================*/
create index EmployeeAddressEmployeeRelationship_FK on EmployeeAddress (
Employee_ID ASC
)
go

/*==============================================================*/
/* Table: Office                                                */
/*==============================================================*/
create table Office (
   Office_ID            numeric              identity,
   Street_ID            numeric              null,
   OfficeName           nvarchar(1024)       not null,
   OfficeInfo           text                 null,
   OfficeHouseNumber    int                  null,
   constraint PK_OFFICE primary key nonclustered (Office_ID)
)
go

/*==============================================================*/
/* Index: OfficeStreetRelationship_FK                           */
/*==============================================================*/
create index OfficeStreetRelationship_FK on Office (
Street_ID ASC
)
go

/*==============================================================*/
/* Table: Payment                                               */
/*==============================================================*/
create table Payment (
   Payment_ID           numeric              identity,
   AutoOrder_ID         numeric              null,
   PaymentValue         money                not null,
   constraint PK_PAYMENT primary key nonclustered (Payment_ID)
)
go

/*==============================================================*/
/* Index: AutoReturningPaymentRelationship2_FK                  */
/*==============================================================*/
create index AutoReturningPaymentRelationship2_FK on Payment (
AutoOrder_ID ASC
)
go

/*==============================================================*/
/* Table: Region                                                */
/*==============================================================*/
create table Region (
   Region_ID            numeric              identity,
   Country_ID           numeric              null,
   RegionName           nvarchar(256)        not null,
   constraint PK_REGION primary key nonclustered (Region_ID)
)
go

/*==============================================================*/
/* Index: CountryRegion_FK                                      */
/*==============================================================*/
create index CountryRegion_FK on Region (
Country_ID ASC
)
go

/*==============================================================*/
/* Table: Street                                                */
/*==============================================================*/
create table Street (
   Street_ID            numeric              identity,
   City_ID              numeric              null,
   StreetName           nvarchar(256)        not null,
   constraint PK_STREET primary key nonclustered (Street_ID)
)
go

/*==============================================================*/
/* Index: CityStreetRelationship_FK                             */
/*==============================================================*/
create index CityStreetRelationship_FK on Street (
City_ID ASC
)
go

/*==============================================================*/
/* Table: SystemUser                                            */
/*==============================================================*/
create table SystemUser (
   SystemUser_ID        numeric              identity,
   Office_ID            numeric              null,
   SystemUserName       nvarchar(15)         not null,
   SystemUserPassword   nvarchar(30)         not null,
   SystemUserRole       nvarchar(50)         not null,
   constraint PK_SYSTEMUSER primary key nonclustered (SystemUser_ID)
)
go

/*==============================================================*/
/* Index: SystemUserWorksInOfficeRelationship_FK                */
/*==============================================================*/
create index SystemUserWorksInOfficeRelationship_FK on SystemUser (
Office_ID ASC
)
go

alter table Auto
   add constraint FK_AUTO_AUTOCLASS_AUTOCLAS foreign key (AutoClass_ID)
      references AutoClass (AutoClass_ID)
go

alter table AutoDamage
   add constraint FK_AUTODAMA_RELATIONS_AUTOORDE foreign key (AutoOrder_ID)
      references AutoOrder (AutoOrder_ID)
go

alter table AutoOrder
   add constraint FK_AUTOORDE_AUTORELAT_AUTO foreign key (Auto_ID)
      references Auto (Auto_ID)
go

alter table AutoOrder
   add constraint FK_AUTOORDE_AUTORETUR_PAYMENT foreign key (Payment_ID)
      references Payment (Payment_ID)
go

alter table AutoOrder
   add constraint FK_AUTOORDE_CHOSEAUTO_AUTOCLAS foreign key (AutoClass_ID)
      references AutoClass (AutoClass_ID)
go

alter table AutoOrder
   add constraint FK_AUTOORDE_CHOSERETU_OFFICE foreign key (Office_ID)
      references Office (Office_ID)
go

alter table AutoOrder
   add constraint FK_AUTOORDE_MANAGERAU_SYSTEMUS foreign key (Sys_SystemUser_ID)
      references SystemUser (SystemUser_ID)
go

alter table AutoOrder
   add constraint FK_AUTOORDE_MANAGERWI_SYSTEMUS foreign key (SystemUser_ID)
      references SystemUser (SystemUser_ID)
go

alter table AutoOrder
   add constraint FK_AUTOORDE_MECHANICC_SYSTEMUS foreign key (Sys_SystemUser_ID2)
      references SystemUser (SystemUser_ID)
go

alter table AutoOrder
   add constraint FK_AUTOORDE_OFFICEREL_OFFICE foreign key (Off_Office_ID)
      references Office (Office_ID)
go

alter table AutoOrder
   add constraint FK_AUTOORDE_ORDERRELA_CLIENT foreign key (Client_ID)
      references Client (Client_ID)
go

alter table City
   add constraint FK_CITY_REGIONCIT_REGION foreign key (Region_ID)
      references Region (Region_ID)
go

alter table ClientAddress
   add constraint FK_CLIENTAD_CLIENTADD_CLIENT foreign key (Client_ID)
      references Client (Client_ID)
go

alter table ClientAddress
   add constraint FK_CLIENTAD_CLIENTADD_STREET foreign key (Street_ID)
      references Street (Street_ID)
go

alter table Employee
   add constraint FK_EMPLOYEE_EMPLOYEEM_SYSTEMUS foreign key (SystemUser_ID)
      references SystemUser (SystemUser_ID)
go

alter table EmployeeAddress
   add constraint FK_EMPLOYEE_EMPLOYEEA_EMPLOYEE foreign key (Employee_ID)
      references Employee (Employee_ID)
go

alter table EmployeeAddress
   add constraint FK_EMPLOYEE_EMPLOYEEA_STREET foreign key (Street_ID)
      references Street (Street_ID)
go

alter table Office
   add constraint FK_OFFICE_OFFICESTR_STREET foreign key (Street_ID)
      references Street (Street_ID)
go

alter table Payment
   add constraint FK_PAYMENT_AUTORETUR_AUTOORDE foreign key (AutoOrder_ID)
      references AutoOrder (AutoOrder_ID)
go

alter table Region
   add constraint FK_REGION_COUNTRYRE_COUNTRY foreign key (Country_ID)
      references Country (Country_ID)
go

alter table Street
   add constraint FK_STREET_CITYSTREE_CITY foreign key (City_ID)
      references City (City_ID)
go

alter table SystemUser
   add constraint FK_SYSTEMUS_SYSTEMUSE_OFFICE foreign key (Office_ID)
      references Office (Office_ID)
go

